IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_tb_ErpProduct')
BEGIN
	DROP VIEW [dbo].View_tb_ErpProduct
END 
GO
create View View_tb_ErpProduct
as
select tb_ErpProduct.ID,Prod_AlarmQuantity,
Prod_Number,Prod_DividedShop,
Prod_Name,Prod_Class,Prod_Unit,
Prod_CostPrice,
Prod_SalesPrice,
Prod_SpecialPrice,
Prod_PurchasePrice,
Prod_IsRetail,
Prod_IsEnabled,
Prod_Introduction,
Prod_Detail,
Prod_Remark,
Prod_Quantity,
Prod_Thumbnail,
Prod_FullSizeImage,
Prod_Expedited,
Prod_CreateDatetime,
Prod_UpdateDatetime,
Prod_Batch,
Prod_ClassName,
Sc_ClassCode,
Sc_ClassName,
Sc_ClassParentID,
Sc_ClassRemark,
Sc_IsDisable,
Sc_IsReadOnly,
Sc_URL,
Sc_IsEnabledURL,
Sc_CreateDatetime,
Sc_UpdateDatetime,
Sc_IsDelete , 
Prod_IsEnabledName=CASE   tb_ErpProduct.Prod_IsEnabled  WHEN 1 THEN '上架' ELSE '下架' END ,
dbo.fn_GetClassCodeToName(Prod_Class,Prod_ClassName) as Prod_ClassName2,
 (select Count(OPlist_ProdQuantity) from tb_ErpOrderProductList where OPlist_ProdNumber =Prod_Number and OPlist_Type = '2' and ((CONVERT(int,OPlist_CompletedStatus)+CONVERT(int,OPlist_PickupStatus))=0)) as Prod_PreSaleQuantity
 ,Prod_Availability
 from tb_ErpProduct left join tb_ErpSystemCategory on tb_ErpProduct.Prod_Class=tb_ErpSystemCategory.Sc_ClassCode
GO